package com.dao.impl;

import com.dao.CommentDao;
import com.entity.Foods;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

public class CommentDaoImpl implements CommentDao {
    private QueryRunner runner=new QueryRunner();
    @Override
    public List<Map<String, Object>> queryCommentsByFoodId(int foodId,int pageIndex,int pageSize) {
        Connection connection = DruidUtils.getConnection();
        List<Map<String, Object>> comments=null;
        int offset=(pageIndex-1)*pageSize;
        String sql = "SELECT c.id,c.`comment`,c.customer_id,c.food_id,c.created_time,u.`name`,u.address,u.image FROM `comment` c,`customers` u WHERE c.food_id=? AND c.customer_id=u.ID order by c.id desc limit ?,?;";
        try {
            comments = runner.query(connection,sql,new MapListHandler(),foodId,offset,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return comments;
    }

    @Override
    public int addCommentByUserID(int userId, String comment, int foodId) {
        Connection connection = DruidUtils.getConnection();
        Timestamp dateNow=new Timestamp(System.currentTimeMillis());
        Date reDate=new Date(System.currentTimeMillis());
        int count=0;
        String sql = "insert into comment set customer_id=?,comment=?,food_id=?,created_time=?;";
        try {
            count = runner.update(connection,sql,userId,comment,foodId,dateNow);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }

    @Override
    public int getCount(int foodId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from comment where food_id=?";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>(),foodId);
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    @Override
    public int deleteComment(int commentId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "delete from comment where id=?;";
        int count = 0;
        try {
            count = runner.update(connection,sql,commentId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }

    @Override
    public int modifyComment(int commentId,String comment) {
        Connection connection = DruidUtils.getConnection();
        String sql = "update comment set comment=? where id=?;";
        int count = 0;
        try {
            count = runner.update(connection,sql,comment,commentId);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return count;
    }


    public static void main(String[] args) {
        CommentDao commentDao=new CommentDaoImpl();
//        System.out.println(commentDao.addCommentByUserID(2,"666",2));
        System.out.println(commentDao.queryCommentsByFoodId(2,1,5));
    }
}
